package com.example.rabc.dao.impl;

import com.example.rabc.dao.RoleDao;
import com.example.rabc.entity.Role;
import com.example.rabc.config.DatabaseConnectionManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * MySQL角色DAO实现类
 */
public class MySqlRoleDao implements RoleDao {
    
    private final DatabaseConnectionManager connectionManager;
    
    public MySqlRoleDao() {
        this.connectionManager = DatabaseConnectionManager.getInstance();
    }

    /**
     * 保存角色到数据库
     * @param entity 实体对象 要保存的角色对象
     * @return 保存后的角色对象
     */
    @Override
    public Role save(Role entity) {
        String sql = "INSERT INTO roles (role_name, role_code, description, status, created_time, updated_time) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            stmt.setString(1, entity.getRoleName());
            stmt.setString(2, entity.getRoleCode());
            stmt.setString(3, entity.getDescription());
            stmt.setInt(4, entity.getStatus());
            stmt.setTimestamp(5, Timestamp.valueOf(entity.getCreatedTime()));
            stmt.setTimestamp(6, Timestamp.valueOf(entity.getUpdatedTime()));
            
            int affectedRows = stmt.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet rs = stmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        entity.setId(rs.getLong(1));
                    }
                }
            }
            return entity;
        } catch (SQLException e) {
            throw new RuntimeException("保存角色失败", e);
        }
    }

    /**
     * 更新数据库中的角色信息
     * @param id 实体ID
     * @param entity 实体对象
     * @return 更新后的角色对象
     */
    @Override
    public Role update(Long id, Role entity) {
        String sql = "UPDATE roles SET role_name=?, role_code=?, description=?, status=?, updated_time=? WHERE id=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, entity.getRoleName());
            stmt.setString(2, entity.getRoleCode());
            stmt.setString(3, entity.getDescription());
            stmt.setInt(4, entity.getStatus());
            stmt.setTimestamp(5, Timestamp.valueOf(entity.getUpdatedTime()));
            stmt.setLong(6, id);
            
            stmt.executeUpdate();
            entity.setId(id);
            return entity;
        } catch (SQLException e) {
            throw new RuntimeException("更新角色失败", e);
        }
    }

    /**
     * 根据角色ID删除数据库中的角色
     * @param id 实体ID
     */
    @Override
    public void deleteById(Long id) {
        String sql = "DELETE FROM roles WHERE id=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setLong(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("删除角色失败", e);
        }
    }

    /**
     * 根据角色ID查询数据库中的角色
     * @param id 实体ID
     * @return 角色对象的Optional包装
     */
    @Override
    public Optional<Role> findById(Long id) {
        String sql = "SELECT * FROM roles WHERE id=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setLong(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询角色失败", e);
        }
        return Optional.empty();
    }

    /**
     * 查询数据库中的所有角色
     * @return 角色列表
     */
    @Override
    public List<Role> findAll() {
        String sql = "SELECT * FROM roles";
        List<Role> roles = new ArrayList<>();
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                roles.add(mapRowToEntity(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询所有角色失败", e);
        }
        return roles;
    }

    /**
     * 分页查询数据库中的所有角色
     * @param page 页码（从0开始）
     * @param size 每页大小
     * @return 分页后的角色列表
     */
    @Override
    public List<Role> findAll(int page, int size) {
        String sql = "SELECT * FROM roles LIMIT ? OFFSET ?";
        List<Role> roles = new ArrayList<>();
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, size);
            stmt.setInt(2, page * size);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    roles.add(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("分页查询角色失败", e);
        }
        return roles;
    }

    /**
     * 统计数据库中的角色数量
     * @return 角色数量
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM roles";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException("统计角色数量失败", e);
        }
        return 0;
    }

    /**
     * 根据角色代码查询数据库中的角色
     * @param roleCode 角色代码
     * @return 角色对象的Optional包装
     */
    @Override
    public Optional<Role> findByRoleCode(String roleCode) {
        String sql = "SELECT * FROM roles WHERE role_code=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, roleCode);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据角色代码查询角色失败", e);
        }
        return Optional.empty();
    }

    /**
     * 根据状态查询数据库中的角色列表
     * @param status 状态
     * @return 角色列表
     */
    @Override
    public List<Role> findByStatus(Integer status) {
        String sql = "SELECT * FROM roles WHERE status=?";
        List<Role> roles = new ArrayList<>();
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, status);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    roles.add(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据状态查询角色失败", e);
        }
        return roles;
    }

    /**
     * 检查数据库中是否存在指定角色代码的角色
     * @param roleCode 角色代码
     * @return 是否存在
     */
    @Override
    public boolean existsByRoleCode(String roleCode) {
        String sql = "SELECT COUNT(*) FROM roles WHERE role_code=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, roleCode);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("检查角色代码是否存在失败", e);
        }
        return false;
    }
    
    /**
     * 将ResultSet行映射为Role实体
     * @param rs ResultSet
     * @return Role实体
     * @throws SQLException SQL异常
     */
    private Role mapRowToEntity(ResultSet rs) throws SQLException {
        Role role = new Role();
        role.setId(rs.getLong("id"));
        role.setRoleName(rs.getString("role_name"));
        role.setRoleCode(rs.getString("role_code"));
        role.setDescription(rs.getString("description"));
        role.setStatus(rs.getInt("status"));
        role.setCreatedTime(rs.getTimestamp("created_time").toLocalDateTime());
        role.setUpdatedTime(rs.getTimestamp("updated_time").toLocalDateTime());
        return role;
    }
}